﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Entities;
using Common.Helper;
using Common.Constants;

namespace DataAccess
{
    public class OrganizationDataAccess
    {
        /// <summary>
        /// Gets the instance.
        /// </summary>
        /// <value>
        /// The instance.
        /// </value>
        public static OrganizationDataAccess Instance { get; private set; }

        /// <summary>
        /// Initializes the <see cref="AddressDataAccess" /> class.
        /// </summary>
        static OrganizationDataAccess()
        {
            Instance = new OrganizationDataAccess();
        }

        /// <summary>
        /// Extracts the raw data to Organization entity
        /// </summary>
        /// <param name="dataRecord">The data record.</param>
        /// <returns></returns>
        private Organisation ExtractRawData(IDataRecord dataRecord)
        {
            var organisation = new Organisation();
            bool isActive;
            if (bool.TryParse(dataRecord["IsActive"].ToString(), out isActive))
                organisation.IsActive = isActive;
            organisation.OrgId = (Guid)dataRecord["OrgId"];
            organisation.OrgName = dataRecord["OrgName"].ToString();
            organisation.ShortDescription = dataRecord["ShortDescription"].ToString();
            organisation.ContactId = dataRecord["ContactId"].ToString();
            organisation.AddressLine1 = dataRecord["AddressLine1"].ToString();
            organisation.AddressLine2 = dataRecord["AddressLine2"].ToString();
            organisation.AddressLine3 = dataRecord["AddressLine2"].ToString();
            bool orgPreferred;
            if (bool.TryParse(dataRecord["OrgPreferred"].ToString(), out orgPreferred))
                organisation.OrgPreferred = orgPreferred;

            bool expressionOfBusiness;
            if (bool.TryParse(dataRecord["ExpressionOfBusiness"].ToString(), out expressionOfBusiness))
                organisation.ExpressionOfBusiness = expressionOfBusiness;
            organisation.BusinessId = (Guid)dataRecord["BusinessId"];
            organisation.AddressId = (Guid)dataRecord["AddressId"];
            organisation.OrgFullDescription = dataRecord["OrgFullDescription"].ToString();
            organisation.PhoneNumber = dataRecord["PhoneNumber"].ToString();
            organisation.Fax = dataRecord["Fax"].ToString();
            organisation.Email = dataRecord["Email"].ToString();
            organisation.WebAddress = dataRecord["WebAddress"].ToString();
            organisation.CharityNumber = dataRecord["CharityNumber"].ToString();
            organisation.CompanyNumber = dataRecord["CompanyNumber"].ToString();

            organisation.Specialism = dataRecord["Specicalism"].ToString();

            organisation.ServicePersonalCircumstances = dataRecord["ServicePersonalCircumstances"].ToString();

            organisation.ServiceDisability = dataRecord["ServiceDisablities"].ToString();


            organisation.ServiceEthnicity = dataRecord["ServiceEthnicity"].ToString();


            organisation.ServiceBarriers = dataRecord["ServiceBarriers"].ToString();


            organisation.Accreditation = dataRecord["Accreditation"].ToString();


            organisation.ServiceBenefits = dataRecord["ServiceBenefits"].ToString();


            organisation.EoiProgrammes = dataRecord["EoiProgrammes"].ToString();

            organisation.EoiService = dataRecord["EOIServices"].ToString();


            return organisation;

        }

        #region add, amend Organization by HieuTT5

        public int InsertOrganisation(Organisation organization)
        {
            if (organization == null)
                return -1;
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("OrgId", organization.OrgId),
                                       new SqlParameter("OrgName", organization.OrgName),
                                       new SqlParameter("ShortDescription", organization.ShortDescription),
                                       new SqlParameter("ContactID", organization.ContactId),
                                       new SqlParameter("AddressLine1", organization.AddressLine1),
                                       new SqlParameter("AddressLine2", organization.AddressLine2),
                                       new SqlParameter("AddressLine3", organization.AddressLine3),
                                       new SqlParameter("AddressId", organization.AddressId),
                                       new SqlParameter("OrgPreferred", organization.OrgPreferred),
                                       new SqlParameter("ExpressionOfBusiness", organization.ExpressionOfBusiness),
                                       new SqlParameter("BusinessId", organization.BusinessId),
                                       new SqlParameter("OrgFullDescription", organization.OrgFullDescription),
                                       new SqlParameter("PhoneNumber", organization.PhoneNumber),
                                       new SqlParameter("Fax", organization.Fax),
                                       new SqlParameter("Email", organization.Email),
                                       new SqlParameter("WebAddress", organization.WebAddress),
                                       new SqlParameter("CharityNumber", organization.CharityNumber),
                                       new SqlParameter("CompanyNumber", organization.CompanyNumber),
                                       new SqlParameter("Specicalism", organization.Specialism),
               
                                       new SqlParameter("ServicePersonalCircumstances",
                                                        organization.ServicePersonalCircumstances),
                                       new SqlParameter("ServiceDisablities", organization.ServiceDisability),
                                       new SqlParameter("ServiceEthnicit", organization.ServiceEthnicity),
                                       new SqlParameter("ServiceBarriers", organization.ServiceBarriers),
                                       new SqlParameter("Accreditation", organization.Accreditation),
                                       new SqlParameter("ServiceBenefits", organization.ServiceBenefits),
                                       new SqlParameter("EOIProgrammes", organization.EoiProgrammes),
                                       new SqlParameter("EOIServices", organization.EoiService),
                                       new SqlParameter("IsActive", organization.IsActive)
                                   };
            var result = SqlHelper.ExecuteNonQuery(ProcedureStores.AddOrganization, colParameter);
            return result;
        }

        public int UpdateOrganization(Organisation organization)
        {
            if (organization == null)
                return -1;
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("OrgId", organization.OrgId),
                                       new SqlParameter("OrgName", organization.OrgName),
                                       new SqlParameter("ShortDescription", organization.ShortDescription),
                                       new SqlParameter("ContactID", organization.ContactId),
                                       new SqlParameter("AddressLine1", organization.AddressLine1),
                                       new SqlParameter("AddressLine2", organization.AddressLine2),
                                       new SqlParameter("AddressLine3", organization.AddressLine3),
                                       new SqlParameter("AddressId", organization.AddressId),
                                       new SqlParameter("OrgPreferred", organization.OrgPreferred),
                                       new SqlParameter("ExpressionOfBusiness", organization.ExpressionOfBusiness),
                                       new SqlParameter("BusinessId", organization.BusinessId),
                                       new SqlParameter("OrgFullDescription", organization.OrgFullDescription),
                                       new SqlParameter("PhoneNumber", organization.PhoneNumber),
                                       new SqlParameter("Fax", organization.Fax),
                                       new SqlParameter("Email", organization.Email),
                                       new SqlParameter("WebAddress", organization.WebAddress),
                                       new SqlParameter("CharityNumber", organization.CharityNumber),
                                       new SqlParameter("CompanyNumber", organization.CompanyNumber),
                                         new SqlParameter("Specicalism", organization.Specialism),
               
                                       new SqlParameter("ServicePersonalCircumstances",
                                                        organization.ServicePersonalCircumstances),
                                       new SqlParameter("ServiceDisablities", organization.ServiceDisability),
                                       new SqlParameter("ServiceEthnicity", organization.ServiceEthnicity),
                                       new SqlParameter("ServiceBarriers", organization.ServiceBarriers),
                                       new SqlParameter("Accreditation", organization.Accreditation),
                                       new SqlParameter("ServiceBenefits", organization.ServiceBenefits),
                                       new SqlParameter("EOIProgrammes", organization.EoiProgrammes),
                                       new SqlParameter("EOIServices", organization.EoiService),
                                       new SqlParameter("IsActive", organization.IsActive)
                                   };
            var result = SqlHelper.ExecuteNonQuery(ProcedureStores.UpdateOrganization, colParameter);
            return result;
        }



        public Organisation GetOrganisationByName(string organizationName)
        {
            if (String.IsNullOrEmpty(organizationName)) return null;
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("orgName", organizationName),
                                   };
            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.GetOrganizationByName, colParameter))
            {
                if (reader.Read())
                {
                    var result = ExtractRawData(reader);
                    return result;
                }
                return null;
            }
        }



        public Organisation GetOrganisationById(string organisationId)
        {
            if (String.IsNullOrEmpty(organisationId)) return null;
            var colParamter = new Collection<SqlParameter>
                                  {
                                      new SqlParameter("OrgId", organisationId)
                                  };
            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.GetOrganizationById, colParamter))
            {
                if (reader.Read())
                {
                    return ExtractRawData(reader);
                }
                return null;
            }

        }


        #endregion

        #region list Organization by ThinhDV

        public Collection<Organisation> ListOrganization(bool includeInactive)
        {

            var lstOrganisation = new Collection<Organisation>();
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("isActive", includeInactive)
                                   };

            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.ListOrganisation, colParameter))
            {
                while (reader.Read())
                {
                    var result = ExtractRawData(reader);
                    lstOrganisation.Add(result);
                }
                return lstOrganisation;

            }
        }

        public int MarkInActive(string orgId)
        {
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("orgId", orgId)
                                   };
            var result = SqlHelper.ExecuteNonQuery(ProcedureStores.OrganisationMarkInActive, colParameter);
            return result;

        }
        public Collection<Organisation> OrganisationOrderByContact(bool modeOrder)

        {
            var lstOrganisation = new Collection<Organisation>();
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("@orderMode", modeOrder)
                                   };

            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.OrganisationOrderByContact, colParameter))
            {
                while (reader.Read())
                {
                    var result = ExtractRawData(reader);
                    lstOrganisation.Add(result);
                }
                return lstOrganisation;

            }
        }
        public Collection<Organisation> OrganisationOrderByAddress(bool modeOrder)
        {
            var lstOrganisation = new Collection<Organisation>();
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("@orderMode", modeOrder)
                                   };

            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.OrganisationOrderByAddress, colParameter))
            {
                while (reader.Read())
                {
                    var result = ExtractRawData(reader);
                    lstOrganisation.Add(result);
                }
                return lstOrganisation;

            }
        }
        #endregion
    }
}
